package com.zouma.service;

import com.zouma.pojo.User;
import com.zouma.utils.DBhepler;
import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: serein
 * @DateTime: 2021/7/6 19:51
 * @Description:
 */
public class UserService {

    /**
     * 查询user表中所有的数据
     * @return
     */
    public static List<User> getAllByDb(){
        List<User> list=new ArrayList<User>();
        try {
            DBhepler db=new DBhepler();
            String sql="select * from user";
            ResultSet rs= db.Search(sql, null);
            while (rs.next()) {
                int id=rs.getInt("id");
                String name=rs.getString("name");
                String pwd=rs.getString("pwd");

                //System.out.println(id+" "+name+" "+pwd);
                list.add(new User(id, name, pwd));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询指定目录中电子表格中所有的数据
     * @param file 文件完整路径
     * @return
     */
    public static List<User> getAllByExcel(String file){
        List<User> list=new ArrayList<User>();
        try {
            Workbook rwb=Workbook.getWorkbook(new File(file));
            Sheet rs=rwb.getSheet("Test Shee 1");//或者rwb.getSheet(0)
            int clos=rs.getColumns();//得到所有的列
            int rows=rs.getRows();//得到所有的行

            System.out.println(clos+" rows:"+rows);
            for (int i = 1; i < rows; i++) {
                for (int j = 0; j < clos; j++) {
                    //第一个是列数，第二个是行数
                    String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                    String name=rs.getCell(j++, i).getContents();
                    String pwd=rs.getCell(j++, i).getContents();

                    System.out.println("id:"+id+" name:"+name+" pwd:"+pwd);
                    list.add(new User(Integer.parseInt(id), name, pwd));
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;

    }

    /**
     * 通过Id判断是否存在
     * @param id
     * @return
     */
    public static boolean isExist(int id){
        try {
            DBhepler db=new DBhepler();
            ResultSet rs=db.Search("select * from user where id=?", new String[]{id+""});
            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }


}
